import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from SKO.AbstractDPJob import AbstractDPJob
from Predict_6626Job import Predict_6626Job
from Predict_6626_2Job import Predict_6626_2Job
from Predict_6627_2Job import Predict_6627_2Job

from Predict_6625Job import Predict_6625Job
class Predict_6627Job(AbstractDPJob):
    def __init__(self,
                 p_mode=None,p_st_no=None, p_aim_st_s=None, p_pret_s_aim =None,p_avg_s_value_before=None,
                 p_avg_s_value_after=None, p_wenjiang_coef=None,
                 p_cao_dh_before=None, p_whisk_time_before=None,
                 p_iron_wt_net=None, p_coef1=None, p_coef2=None,
                 p_coef3=None, p_coef4=None, p_coef5=None,
                 p_iron_price=None, p_wenjiang_price=None, p_cao_price=None):
        super(Predict_6627Job, self).__init__()
        self.mode = p_mode
        self.st_no = p_st_no
        self.aim_st_s = p_aim_st_s
        self.pret_s_aim = p_pret_s_aim
        self.avg_s_value_before = p_avg_s_value_before
        self.avg_s_value_after = p_avg_s_value_after
        self.wenjiang_coef = p_wenjiang_coef
        self.cao_dh_before = p_cao_dh_before
        self.whisk_time_before = p_whisk_time_before
        self.iron_wt_net = p_iron_wt_net
        self.coef1 = p_coef1
        self.coef2 = p_coef2
        self.coef3 = p_coef3
        self.coef4 = p_coef4
        self.coef5 = p_coef5
        self.iron_price = p_iron_price
        self.wenjiang_price = p_wenjiang_price
        self.cao_price = p_cao_price
        pass
    def execute(self):
        return self.do_execute()
    def do_execute(self):
        super(Predict_6627Job, self).do_execute()
        # 预测二炼钢硫平衡接口传入参数
        # 出钢记号、计划钢种目标S、改变前的铁水S与改变后的铁水S
        mode = self.mode
        st_no = self.st_no
        aim_st_s = self.aim_st_s
        pret_s_aim = self.pret_s_aim
        avg_s_value_before = self.avg_s_value_before
        avg_s_value_after = self.avg_s_value_after
        # 改变前的温降、脱硫剂单耗、脱硫时间
        wenjiang_coef = self.wenjiang_coef
        cao_dh_before = self.cao_dh_before
        whisk_time_before = self.whisk_time_before

        iron_wt_net = self.iron_wt_net
        coef1 = self.coef1
        coef2 = self.coef2
        coef3 = self.coef3
        coef4 = self.coef4
        coef5 = self.coef5
        iron_price = self.iron_price
        wenjiang_price = self.wenjiang_price
        cao_price = self.cao_price
        DB_HOST_MPP_DB2 = '10.70.48.41'
        DB_PORT_MPP_DB2 = 50021
        DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        DB_USER_MPP_DB2 = 'm1admin'
        DB_PASSWORD_MPP_DB2 = 'm1adminbdzg'

        def getConnectionDb2(host, port, dbname, user, password):
            # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
            engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                   encoding="utf-8", poolclass=NullPool)
            return engine.connect()

        # db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
        #                                DB_PORT_MPP_DB2,
        #                                DB_DBNAME_MPP_DB2,
        #                                DB_USER_MPP_DB2,
        #                                DB_PASSWORD_MPP_DB2)
        # 根据出钢记号、计划钢种目标S，进行分组
        if st_no == 'IH2554A2':
            group = 1
            sql_condition = " AND ST_NO = 'IH2554A2'"
        elif st_no[:2] == 'IH' and st_no != 'IH2554A2':
            group = 2
            sql_condition = " AND ST_NO != 'IH2554A2' AND left(ST_NO,2) = 'IH' "
        elif st_no[:2] == 'IW':
            group = 3
            sql_condition = " AND left(ST_NO,2) = 'IW' "
        elif aim_st_s <= 15 and st_no[:2] not in ['IH', 'IW']:
            group = 4
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S <= 15"
        elif aim_st_s <= 20 and aim_st_s > 15 and st_no[:2] not in ['IH', 'IW']:
            group = 5
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 15 AND AIM_ST_S <= 20"
        elif aim_st_s <= 30 and aim_st_s > 20 and st_no[:2] not in ['IH', 'IW']:
            group = 6
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 20 AND AIM_ST_S <= 30"
        elif aim_st_s <= 50 and aim_st_s > 30 and st_no[:2] not in ['IH', 'IW']:
            group = 7
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 30 AND AIM_ST_S <= 50"
        elif aim_st_s <= 100 and aim_st_s > 50 and st_no[:2] not in ['IH', 'IW']:
            group = 8
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 50 AND AIM_ST_S <= 100"
        elif aim_st_s < 150 and aim_st_s > 100 and st_no[:2] not in ['IH', 'IW']:
            group = 9
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 100 AND AIM_ST_S < 150"
        elif aim_st_s < 180 and aim_st_s >= 150 and st_no[:2] not in ['IH', 'IW']:
            group = 10
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S >= 150 AND AIM_ST_S < 180"
        elif aim_st_s <= 250 and aim_st_s >= 180 and st_no[:2] not in ['IH', 'IW']:
            group = 11
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S >= 180 AND AIM_ST_S <= 250"
        else:
            group = 12
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 250"
        #本地编写目前读取本地文件，后续使用sql_condition进行拼接SQL
        start = datetime.datetime.now()
        delta_day2 = 10
        delta_day1 = delta_day2 + 365
        p_day_2 = (start - datetime.timedelta(days=delta_day2)).strftime("%Y%m%d")
        p_day_1 = (start - datetime.timedelta(days=delta_day1)).strftime("%Y%m%d")
        sql = " select *  " \
              " FROM BGTAMOMMSM.T_ODS_TMMSM0701  " \
              " WHERE TC_PROC_FlAG = 2 AND PROD_DATE>='%s' " \
              " AND PROD_DATE<'%s' " \
              " %s ORDER BY PROD_DATE " % (p_day_1, p_day_2, sql_condition)
        print(sql)
        # df0 = pd.read_sql_query(sql, con=db_conn_mpp)
        # col_list = []
        # col_list.append('PROD_DATE')
        # col_list.append('ST_NO')
        # col_list.append('AIM_ST_S')
        # col_list.append('ACT_ST_S')
        # col_list.append('PRET_S_AIM')
        # col_list.append('REVISE_PRET_S')
        # col_list.append('IRON_WT_TOTAL')
        # # col_list.append('IRON_WT_NET')
        # col_list.append('STTEMP_AFIRON')
        # col_list.append('RECV_SI')
        # col_list.append('RECV_S')
        # col_list.append('RECV_TI')
        # # col_list.append('AFTTEMP_RON1')
        # col_list.append('AFTEMP_AFIRON')
        # col_list.append('CAO_LOSS_SUM')
        # col_list.append('CAO_DH')
        # col_list.append('DES_WHISK_DEPTH1')
        # print(col_list)
        #
        # df0.columns = df0.columns.str.upper()
        # df0['PROD_DATE'] = df0['PROD_DATE'].astype(str)
        # df = df0[col_list]
        # # df0['dh1'] = df0['CAO_LOSS_SUM'] / df0['IRON_WT_NET'] * 100
        # df['dh2'] = df['CAO_LOSS_SUM'] / df['IRON_WT_TOTAL'] * 100
        # # dh2 int 就是CAO_DH
        # df['temp'] = df['STTEMP_AFIRON'] - df['AFTEMP_AFIRON']











        xlsx_name = 'D:/repos/sicost/group' + str(int(group)) + '_new.xlsx'
        df = pd.read_excel(xlsx_name)
        if df.empty is True:
            msg = '该钢种无分组'
        df.columns = df.columns.str.upper()
        df['PROD_DATE'] = df['PROD_DATE'].astype(str)

        if pret_s_aim == '':
            print('需要计算KR目标S')
            msg, pret_s_aim = Predict_6625Job(p_mode=1,p_st_no=st_no, p_aim_st_s=aim_st_s).execute()
        pret_s_aim = int(pret_s_aim)
        if mode==1:
            xlsx_name = 'D:/repos/sicost/二炼钢脱硫模型单耗.xlsx'
            df2 = pd.read_excel(xlsx_name)
            xlsx_name = 'D:/repos/sicost/二炼钢脱硫模型时间.xlsx'
            df3 = pd.read_excel(xlsx_name)
        else:
            df2, df3 = Predict_6627_2Job()

        df2.columns = df2.columns.str.upper()
        df3.columns = df3.columns.str.upper()

        df2['AVG'] = 0.33 * df2['IW'] + 0.17 * (df2['AIM_14'] + df2['AIM_24']) + 0.17 * (df2['AIM_34'] + df2['AIM_54'])
        row1 = df2[df2['RECV_S_MAX'] == 300]
        value1 = row1['AVG'].values[0] if not row1.empty else 0
        row2 = df2[df2['RECV_S_MAX'] == 500]
        value2 = row2['AVG'].values[0] if not row2.empty else 0
        dh_unit_1 = (value2 - value1) / 20
        # 受铁后S
        # recv_s = df1['RECV_S'].values[0]
        column_name_list = df2.columns.tolist()
        print(column_name_list)
        column_name_tmp = 'AIM_' + str(int(pret_s_aim))
        df2_tmp = df2[['RECV_S_MAX', column_name_tmp]]
        if st_no[:2] == 'IW':
            column_name_tmp = 'IW'
        df2_tmp = df2[['RECV_S_MAX', column_name_tmp]]
        # 单耗,CAO复合单耗
        df2_tmp.rename(columns={column_name_tmp: 'DH'}, inplace=True)
        df2_tmp['RECV_S_MAX_NEXT'] = df2_tmp['RECV_S_MAX'].shift(-1)
        df2_tmp['DH_NEXT'] = df2_tmp['DH'].shift(-1)
        df2_tmp.RECV_S_MAX_NEXT.fillna(99999, inplace=True)
        df2_tmp.DH_NEXT.fillna(99999, inplace=True)
        # xlsx_name = 'D:/repos/sicost/二炼钢脱硫模型时间.xlsx'
        # df3 = pd.read_excel(xlsx_name)
        # df3.columns = df3.columns.str.upper()
        row3 = df3[df3['RECV_S_MAX'] == 300]
        value3 = row3['WHISK_TIME'].values[0] if not row3.empty else 0
        row4 = df3[df3['RECV_S_MAX'] == 500]
        value4 = row4['WHISK_TIME'].values[0] if not row4.empty else 0
        whisk_time_unit_1 = (value4 - value3) / 20
        df3['RECV_S_MAX_NEXT'] = df3['RECV_S_MAX'].shift(-1)
        df3['WHISK_TIME_NEXT'] = df3['WHISK_TIME'].shift(-1)
        df3.RECV_S_MAX_NEXT.fillna(99999, inplace=True)
        df3.WHISK_TIME_NEXT.fillna(99999, inplace=True)

        def cal_dh_whisk_time(p_recv_s):
            recv_s = p_recv_s
            for index, row in df2_tmp.iterrows():
                recv_s_max_tmp = row['RECV_S_MAX']
                dh_max_tmp = row['DH']
                recv_s_max_next_tmp = row['RECV_S_MAX_NEXT']
                dh_max_next_tmp = row['DH_NEXT']
                print(index)
                if recv_s <= 500:
                    dh_unit = (dh_max_next_tmp - dh_max_tmp) / (recv_s_max_next_tmp - recv_s_max_tmp) * 10
                else:
                    dh_unit = dh_unit_1
                if index == 0:
                    if recv_s <= recv_s_max_tmp:
                        dh_tmp = dh_max_tmp - (recv_s_max_tmp - recv_s) * dh_unit / 10
                        break
                    elif recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                        dh_tmp = dh_max_tmp + (recv_s - recv_s_max_tmp) * dh_unit
                        break
                    else:
                        continue
                else:
                    if recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                        dh_tmp = dh_max_tmp + (recv_s - recv_s_max_tmp) * dh_unit / 10
                        break
                    else:
                        continue
            for index, row in df3.iterrows():
                recv_s_max_tmp = row['RECV_S_MAX']
                whisk_time_max_tmp = row['WHISK_TIME']
                recv_s_max_next_tmp = row['RECV_S_MAX_NEXT']
                whisk_time_max_next_tmp = row['WHISK_TIME_NEXT']
                print(index)
                if recv_s <= 500:
                    whisk_time_unit = (whisk_time_max_next_tmp - whisk_time_max_tmp) / (
                            recv_s_max_next_tmp - recv_s_max_tmp) * 10
                else:
                    whisk_time_unit = whisk_time_unit_1
                if index == 0:
                    if recv_s <= recv_s_max_tmp:
                        whisk_time_tmp = whisk_time_max_tmp - (recv_s_max_tmp - recv_s) * whisk_time_unit / 10
                        break
                    elif recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                        whisk_time_tmp = whisk_time_max_tmp + (recv_s - recv_s_max_tmp) * whisk_time_unit
                        break
                    else:
                        continue
                else:
                    if recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                        whisk_time_tmp = whisk_time_max_tmp + (recv_s - recv_s_max_tmp) * whisk_time_unit / 10
                        break
                    else:
                        continue
            return dh_tmp, whisk_time_tmp

        # recv_s_before = 30
        # recv_s_after = 50
        recv_s_before = avg_s_value_before
        recv_s_after = avg_s_value_after
        recv_s_delta = recv_s_after - recv_s_before
        dh_tmp, whisk_time_tmp = cal_dh_whisk_time(recv_s_before * 10)
        if cao_dh_before != '':
            cao_dh_before = float(cao_dh_before)
        else:
            cao_dh_before = dh_tmp
        if whisk_time_before != '':
            whisk_time_before = float(whisk_time_before)
        else:
            whisk_time_before = whisk_time_tmp
        cao_dh_after, whisk_time_after = cal_dh_whisk_time(recv_s_after * 10)
        # iron_wt = df1['IRON_WT_NET'].values[0] / 10
        dh_tmp = cao_dh_before
        dh_tmp_ad = cao_dh_after
        whisk_time_tmp = whisk_time_before
        whisk_time_tmp_ad = whisk_time_after
        whisk_time_tmp_delta = whisk_time_tmp_ad - whisk_time_tmp
        # iron_wt = 270
        iron_wt = iron_wt_net/10
        cao_wt = dh_tmp * iron_wt / 1000
        cao_wt_ad = dh_tmp_ad * iron_wt / 1000
        cao_wt_delta = cao_wt_ad - cao_wt
        # xlsx_name = 'D:/repos/sicost/二炼钢成本参数.xlsx'
        # df3 = pd.read_excel(xlsx_name)
        # coef1 = df3['炉均扒渣量'].values[0]
        # coef2 = df3['高炉渣'].values[0]
        # coef3 = df3['扒渣量增加'].values[0]
        # coef4 = df3['铁水包成本'].values[0]
        # coef5 = df3['搅拌桨成本'].values[0]
        # coef6 = df3['热轧品种的边际贡献'].values[0]
        # 扒渣带铁量(t/包)
        coef7 = coef1 - coef2 - cao_wt
        # 30S扒渣带铁比例(%)
        coef8 = coef7 / coef1
        # 50S扒渣带铁比例(%)
        coef9 = coef8 + 0.1
        # 耐材增加/S
        coef10 = whisk_time_tmp_delta / whisk_time_tmp / recv_s_delta
        # 扒渣带铁
        daitie = coef1 * coef8 / iron_wt
        daitie_ad = (coef1 + cao_wt_delta) * (1 + coef3 * recv_s_delta) * coef9 / iron_wt

        if wenjiang_coef != '':
            wenjiang_coef = float(wenjiang_coef)
            wenjiang = wenjiang_coef * whisk_time_tmp
            wenjiang_ad = wenjiang_coef * whisk_time_tmp_ad
        else:
            # 温降与脱硫时间的转换系数，如果没有传入则使用历史数据拟合
            # msg, wenjiang_coef_tmp = Predict_6626Job(p_mode=1, p_st_no=st_no, p_aim_st_s=aim_st_s).execute()
            # # wenjiang_coef_tmp = 1
            # wenjiang_coef = wenjiang_coef_tmp
            # wenjiang = wenjiang_coef * whisk_time_tmp
            # wenjiang_ad = wenjiang_coef * whisk_time_tmp_ad
            wenjiang = Predict_6626_2Job(p_st_no=st_no, p_aim_st_s=aim_st_s,p_recv_s=recv_s_before).execute()
            wenjiang_ad = Predict_6626_2Job(p_st_no=st_no, p_aim_st_s=aim_st_s,p_recv_s=recv_s_after).execute()


        # temp_down = 35
        # temp_down_ad = whisk_time_tmp_ad / whisk_time_tmp * temp_down
        # 耐材成本
        naicai_cost = coef4 + coef5
        naicai_cost_ad = naicai_cost * recv_s_delta * coef10
        # 带铁成本
        # iron_price = 3000
        daitie_cost = daitie * iron_price
        daitie_cost_ad = daitie_ad * iron_price
        # 温降成本
        # temp_down_price = 1
        wenjiang_cost = wenjiang * wenjiang_price
        wenjiang_cost_ad = wenjiang_ad * wenjiang_price
        # 脱硫剂成本
        # cao_price = 0.91
        tuoliuji_cost = dh_tmp * cao_price
        tuoliuji_cost_ad = dh_tmp_ad * cao_price

        total_cost = naicai_cost + daitie_cost + wenjiang_cost + tuoliuji_cost
        total_cost_ad = naicai_cost_ad + daitie_cost_ad + wenjiang_cost_ad + tuoliuji_cost_ad
        df_out = pd.DataFrame(columns=['PLAN_NAME', 'CAO_DH', 'WHISK_TIME', 'DAITIE', 'WENJIANG',
                                       'NAICAI_COST', 'DAITIE_COST', 'WENJIANG_COST', 'TUOLIUJI_COST', 'TOTAL_COST'])
        dict = {}

        dict['PLAN_NAME'] = '调整铁水硫前'
        dict['CAO_DH'] = dh_tmp
        dict['WHISK_TIME'] = whisk_time_tmp
        dict['DAITIE'] = daitie
        dict['WENJIANG'] = wenjiang
        dict['NAICAI_COST'] = naicai_cost
        dict['DAITIE_COST'] = daitie_cost
        dict['WENJIANG_COST'] = wenjiang_cost
        dict['TUOLIUJI_COST'] = tuoliuji_cost
        dict['TOTAL_COST'] = total_cost
        new_row = pd.Series(dict)
        df_out = df_out.append(new_row, ignore_index=True)
        dict = {}

        dict['PLAN_NAME'] = '调整铁水硫后'
        dict['CAO_DH'] = dh_tmp_ad
        dict['WHISK_TIME'] = whisk_time_tmp_ad
        dict['DAITIE'] = daitie_ad
        dict['WENJIANG'] = wenjiang_ad
        dict['NAICAI_COST'] = naicai_cost_ad
        dict['DAITIE_COST'] = daitie_cost_ad
        dict['WENJIANG_COST'] = wenjiang_cost_ad
        dict['TUOLIUJI_COST'] = tuoliuji_cost_ad
        dict['TOTAL_COST'] = total_cost_ad
        new_row = pd.Series(dict)
        df_out = df_out.append(new_row, ignore_index=True)
        msg = '运行成功'
        result_list = df_out.to_dict(orient='records')
        return msg, result_list